package com.apes.hr.transfer.repository;

import com.apes.framework.jpa.repository.CustomRepository;

import com.apes.hr.base.model.Employee;
import com.apes.hr.base.model.WorkPost;
import com.apes.hr.transfer.model.TransferApplication;
import com.apes.hr.transfer.model.TransferApplicationItem;
import com.apes.scm.masterdata.enumeration.model.Enumeration;
import com.apes.scm.masterdata.frame.model.Branch;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * @author Fengjiajing
 * @create 2018-07-12 10:07
 */
public interface TransferApplicationItemRepository extends CustomRepository<TransferApplicationItem, Integer>{

    List<TransferApplicationItem> findAllById(int yItemId);

    List<TransferApplicationItem> findByTransferUidAndIconExecute(Employee transferUid,boolean iconExecute);

    Set<TransferApplicationItem> findByTransferApplications(TransferApplication transferApplication);

    @Modifying
    @Query("UPDATE TransferApplicationItem SET iconExecute = ?2 WHERE id = ?1")
    void updateItemIconExecute(Integer id, boolean iconExecute);

    @Modifying
    @Query(value = "UPDATE hr_transfer_application_item SET ROLE_LIST =?1 WHERE ID = ?2", nativeQuery = true)
    void updateItemRoles(String roleList, String itemId);
//
//    @Query(value = "SELECT T from TransferApplicationItem T where t.branch =?1 and t.workPost=?2 and t.iconExecute =?3")
//    List<TransferApplicationItem> findByBranchAndWorkPostAndIconExecute( Branch branch, WorkPost workPost , boolean icon);
    @Query(value = "SELECT t.* from hr_transfer_application_item t where t.branch_id_after =?1 and t.post_id_after=?2 and t.icon_execute =?3",nativeQuery = true)
    List<TransferApplicationItem> findByBranchAfterAndWorkPostAfterAndIconExecute( Branch branch, WorkPost workPost , boolean icon);

    List<TransferApplicationItem> findByBranchAndWorkPostAndIconExecute(Branch branch,WorkPost workPost,boolean icon);
    @Query(value = "SELECT t.*  from hr_transfer_application_item t WHERE t.transfer_uid =?1 AND t.icon_execute = ?2 AND t.effective_date >= ?3",nativeQuery = true)
    List<TransferApplicationItem> findByTransferUidAndIconExecuteAndEffectiveDate(Employee transferUid, boolean iconExecute, Date effectiveDate);


    @Query(value = "SELECT T.* FROM HR_TRANSFER_APPLICATION A, HR_TRANSFER_APPLICATION_ITEM T WHERE T.TRANSFER_APPLICATION_ID = A.ID " +
            "AND A.TRANSFER_TYPE = 000102 AND T.ICON_EXECUTE = 1 AND T.REPEAL = 0 AND A.STATE != 'delete'" +
            "AND t.effective_date >= ?3 and t.effective_date <= ?4  AND t.post_id IN ?1  AND t.on_duty_id IN ?2" ,nativeQuery = true)
    List<TransferApplicationItem> findByWorkPostAndOnDutyState(List<WorkPost> workPost, Enumeration onDutyState, Date reward , Date endDate);

    @Query(value = "SELECT T.* FROM HR_TRANSFER_APPLICATION A, HR_TRANSFER_APPLICATION_ITEM T WHERE T.TRANSFER_APPLICATION_ID = A.ID " +
            "AND A.TRANSFER_TYPE = 000102 AND T.ICON_EXECUTE = 1 AND T.REPEAL = 0 AND A.STATE != 'delete'" +
            "AND add_months(t.effective_date,2) >= ?3  AND t.post_id IN ?1  AND t.on_duty_id IN ?2" ,nativeQuery = true)
    List<TransferApplicationItem> findByWorkPostAndOnDutyStateAndEffectiveDate(List<WorkPost> workPost, Enumeration onDutyState,Date reward );


    @Query(value = "SELECT T.*" +
            "  FROM HR_TRANSFER_APPLICATION_ITEM T, HR_TRANSFER_APPLICATION A" +
            " WHERE A.ID = T.TRANSFER_APPLICATION_ID" +
            "   AND A.TRANSFER_TYPE = '000103'" +
            "   AND a.state != 'create' " +
            "   AND a.state != 'delete'" +
            "   AND t.end_date - t.effective_date > 7" +
            "   AND t.transfer_uid = ?1" +
            "   AND ((t.effective_date <= ?2  and t.end_date >= ?2)" +
            "   OR (t.effective_date >= ?2 and t.effective_date <= ?3 ))" ,nativeQuery = true)
    List<TransferApplicationItem> findToRewardPunish(String employeeId, Date reward , Date endDate);

    /**
     * 查询有效支援人员
     * @param branch
     * @param reward
     * @return
     */
    @Query(value = "SELECT t.*  FROM hr_transfer_application_item t,hr_transfer_application b WHERE " +
            "t.transfer_application_id = b.id and b.transfer_type = '000103' and " +
            "t.branch_id_after = :branch and t.effective_date <= :reward" +
            " and t.end_date >= :reward and t.icon_execute=1 and t.repeal =0" ,nativeQuery = true)
    List<TransferApplicationItem> findToSup(@Param("branch") Branch branch, @Param("reward")Date reward );

}
